﻿using Dapper;
using Estsh.Core.Base;
using Estsh.Core.Dapper;
using Estsh.Core.Models;
using Estsh.Core.Repositories;
using Estsh.Core.Wms.IRepositories;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Estsh.Core.Wms.Repositories
{
	public class NCMoveRepository : BaseRepository<BaseEntity>, INCMoveRepository
	{
		public NCMoveRepository(DapperDbContext _dapperDbContext) : base(_dapperDbContext)
		{

		}
		/// <summary>
		/// 获取库位与零件对应关系
		/// </summary>
		/// <param name="locateName"></param>
		/// <returns></returns>
		public List<SysLocate> CheckLocateStatus(string locateName)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				string result = string.Empty;

				StringBuilder sql = new StringBuilder(1024);
				sql.Append(@"SELECT a.* FROM dbo.sys_locate a (nolock) WHERE a.locate_name =@locateName AND a.enabled='Y'");
				DynamicParameters parameters = new DynamicParameters();
				parameters.Add("@locateName", locateName);

				return dbConn.Query<SysLocate>(sql.ToString(), parameters).ToList();
			}
		}

		/// <summary>
		/// 获取NC库位 已使用容量
		/// </summary>
		/// <param name="locateName"></param>
		/// <returns></returns>
		public List<SysStock> CheckUseStockCapacity(string locateName)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				string result = string.Empty;

				StringBuilder sql = new StringBuilder(1024);
				sql.Append(" SELECT * FROM dbo.sys_stock (nolock) WHERE locate_name = @locateName AND status=90 and enabled='Y'  ");
				DynamicParameters parameters = new DynamicParameters();
				parameters.Add("@locateName", locateName);
				return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
			}
		}

		/// <summary>
		/// 获取箱条码状态
		/// </summary>
		/// <param name="cartonNo"></param>
		/// <returns></returns>
		public SysStock CheckCartonNoStatus(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				string result = string.Empty;

				StringBuilder sql = new StringBuilder(1024);
				sql.Append("select a.*,b.enum_name as stock_status from sys_stock (nolock) a left join sys_enum b on enum_type='sys_stock_status' and enum_value=status where carton_no=@carton_no  AND a.enabled='Y' ");
				DynamicParameters parameters = new DynamicParameters();
				parameters.Add("@carton_no", cartonNo);

				return dbConn.QueryFirstOrDefault<SysStock>(sql.ToString(), parameters);
			}
		}

		/// <summary>
		/// NC修改库位
		/// </summary>
		/// <param name="oldStock"></param>
		/// <param name="locate"></param>
		/// <param name="status">条码状态</param>
		/// <param name="TransType">事务类型</param>
		/// <param name="loginId"></param>
		/// <returns></returns>
		public string UpdateStockStatus(List<SysStock> oldStock, List<SysLocate> locate, int status, int TransType, string loginId)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();

				List<string> sqlStrings = new List<string>();
				List<DynamicParameters> parameterList = new List<DynamicParameters>();

				for (int i = 0; i < oldStock.Count; i++)
				{
					//更新条码表，状态 50 改变为 50 已上架
					StringBuilder updateString = new StringBuilder(1024);
					updateString.Append("update sys_stock set status=@status,zone_id=@zone_id,zone_name=@zone_name,locate_id=@locate_id,locate_name=@locate_name, update_userid=@update_userid,update_time=@update_time   where guid=@guid ");
					DynamicParameters updateStringparameters = new DynamicParameters();
					updateStringparameters.Add("@status", status);
					updateStringparameters.Add("@locate_id", locate[0].LocateId);
					updateStringparameters.Add("@locate_name", locate[0].LocateName);
					updateStringparameters.Add("@zone_id", locate[0].ZoneId);
					updateStringparameters.Add("@zone_name", locate[0].ZoneName);
					updateStringparameters.Add("@guid", oldStock[i].Guid);
					updateStringparameters.Add("@update_userid", loginId);
					updateStringparameters.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));

					sqlStrings.Add(updateString.ToString());
					parameterList.Add(updateStringparameters);

					//新增 sys_stock_trans 事务表
					StringBuilder updateString4 = new StringBuilder(1024);
					updateString4.Append(@" INSERT INTO[dbo].[sys_stock_trans]
                                                    ([trans_code]
                                                    ,[carton_no]
                                                    ,[part_id]
                                                    ,[part_no]
                                                    ,[part_spec]
                                                    ,[src_locate_id]
                                                    ,[src_locate_name]
                                                    ,[dest_locate_id]
                                                    ,[dest_locate_name]
                                                    ,[old_qty]
                                                    ,[new_qty]
                                                    ,[trans_qty]
                                                    ,[old_status]
                                                    ,[new_status]
                                                    ,[old_qms_status]
                                                    ,[new_qms_status]
                                                    ,[unit]
                                                    ,[factory_id]
                                                    ,[factory_code]
                                                    ,[src_erp_warehouse]
                                                    ,[dest_erp_warehouse]
                                                    ,[src_warehouse_id]
                                                    ,[src_warehouse_name]
                                                    ,[dest_warehouse_id]
                                                    ,[dest_warehouse_name]
                                                    ,[src_zone_id]
                                                    ,[src_zone_name]
                                                    ,[dest_zone_id]
                                                    ,[dest_zone_name]
                                                    ,[enabled]
                                                    ,[update_userid]
                                                    ,[update_time]
                                                    ,[create_userid]
                                                    ,[create_time]
                                                    ,[guid])
                                                  VALUES (@trans_code,@carton_no,@part_id,@part_no,@part_spec,@src_locate_id ,@src_locate_name ,@dest_locate_id ,@dest_locate_name ,@old_qty 
                                        ,@new_qty ,@trans_qty ,@old_status ,@new_status ,@old_qms_status ,@new_qms_status ,@unit ,@factory_id ,@factory_code ,@src_erp_warehouse ,@dest_erp_warehouse ,@src_warehouse_id ,@src_warehouse_name ,@dest_warehouse_id ,@dest_warehouse_name ,@src_zone_id ,@src_zone_name ,@dest_zone_id ,@dest_zone_name ,@enabled ,@update_userid ,@update_time ,@create_userid ,@create_time ,@guid )");

					DynamicParameters updateStringparameters4 = new DynamicParameters();
					updateStringparameters4.Add("@trans_code", TransType);
					updateStringparameters4.Add("@carton_no", oldStock[i].CartonNo);
					updateStringparameters4.Add("@part_id", oldStock[i].PartId);
					updateStringparameters4.Add("@part_no", oldStock[i].PartNo);
					updateStringparameters4.Add("@part_spec", oldStock[i].PartSpec);
					updateStringparameters4.Add("@src_locate_id", oldStock[i].LocateId);
					updateStringparameters4.Add("@src_locate_name", oldStock[i].LocateName);
					updateStringparameters4.Add("@dest_locate_id", locate[0].LocateId);
					updateStringparameters4.Add("@dest_locate_name", locate[0].LocateName);
					updateStringparameters4.Add("@old_qty", oldStock[i].Qty);
					updateStringparameters4.Add("@new_qty", oldStock[i].Qty);
					updateStringparameters4.Add("@trans_qty", oldStock[i].Qty);
					updateStringparameters4.Add("@old_status", oldStock[i].Status);
					updateStringparameters4.Add("@new_status", status);
					updateStringparameters4.Add("@old_qms_status", oldStock[i].QmsStatus);
					updateStringparameters4.Add("@new_qms_status", oldStock[i].QmsStatus);
					updateStringparameters4.Add("@unit", oldStock[i].Unit);
					updateStringparameters4.Add("@factory_id", oldStock[i].FactoryId);
					updateStringparameters4.Add("@factory_code", oldStock[i].FactoryCode);
					updateStringparameters4.Add("@src_erp_warehouse", oldStock[i].ErpWarehouse);
					updateStringparameters4.Add("@dest_erp_warehouse", oldStock[i].ErpWarehouse);
					updateStringparameters4.Add("@src_warehouse_id", oldStock[i].WarehouseId);
					updateStringparameters4.Add("@src_warehouse_name", oldStock[i].WarehouseName);
					updateStringparameters4.Add("@dest_warehouse_id", locate[0].WarehouseId);
					updateStringparameters4.Add("@dest_warehouse_name", locate[0].WarehouseName);
					updateStringparameters4.Add("@src_zone_id", oldStock[i].ZoneId);
					updateStringparameters4.Add("@src_zone_name", oldStock[i].ZoneName);
					updateStringparameters4.Add("@dest_zone_id", locate[0].ZoneId);
					updateStringparameters4.Add("@dest_zone_name", locate[0].ZoneName);
					updateStringparameters4.Add("@enabled", oldStock[i].Enabled);
					updateStringparameters4.Add("@update_userid", loginId);
					updateStringparameters4.Add("@update_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
					updateStringparameters4.Add("@create_userid", loginId);
					updateStringparameters4.Add("@create_time", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"));
					updateStringparameters4.Add("@guid", Guid.NewGuid());

					sqlStrings.Add(updateString4.ToString());
					parameterList.Add(updateStringparameters4);

				}
				//执行事务
				IDbTransaction transaction = dbConn.BeginTransaction();

				bool successCount = true;
				for (int i = 0; i < sqlStrings.Count; i++)
				{
					if (dbConn.Execute(sqlStrings[i], parameterList[i], transaction) < 1)
					{
						successCount = false;
					}
				}
				if (successCount)
				{
					transaction.Commit();
					return "OK";
				}
				else
				{
					transaction.Rollback();
					return "操作失败，请重新尝试！";
				}
			}
		}


		public List<SysStock> GetCartonNoInfo(string cartonNo)
		{
			using (IDbConnection dbConn = dapperDbContext.GetDbConnection())
			{
				dbConn.Open();
				string result = string.Empty;

				StringBuilder sql = new StringBuilder(1024);
				sql.Append("select * from sys_stock (nolock) where carton_no= @carton_no ");
				DynamicParameters parameters = new DynamicParameters();
				parameters.Add("@carton_no", cartonNo);

				return dbConn.Query<SysStock>(sql.ToString(), parameters).ToList();
			}
		}

	}
}
